{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>电器产品</th>\n",
       "      <th>业务员</th>\n",
       "      <th>时间</th>\n",
       "      <th>城市</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李可</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>541</td>\n",
       "      <td>125</td>\n",
       "      <td>67625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李可</td>\n",
       "      <td>秋季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>674</td>\n",
       "      <td>125</td>\n",
       "      <td>84250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李亮</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>720</td>\n",
       "      <td>125</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李亮</td>\n",
       "      <td>夏季</td>\n",
       "      <td>上海</td>\n",
       "      <td>641</td>\n",
       "      <td>125</td>\n",
       "      <td>80125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MP3</td>\n",
       "      <td>张平</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>721</td>\n",
       "      <td>125</td>\n",
       "      <td>90125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MP3</td>\n",
       "      <td>张平</td>\n",
       "      <td>夏季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>384</td>\n",
       "      <td>125</td>\n",
       "      <td>48000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MP3</td>\n",
       "      <td>周顺利</td>\n",
       "      <td>夏季</td>\n",
       "      <td>上海</td>\n",
       "      <td>354</td>\n",
       "      <td>125</td>\n",
       "      <td>44250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MP3</td>\n",
       "      <td>周顺利</td>\n",
       "      <td>秋季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>841</td>\n",
       "      <td>125</td>\n",
       "      <td>105125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  电器产品  业务员  时间  城市   数量   单价     销售额\n",
       "0  MP3   李可  春季  上海  541  125   67625\n",
       "1  MP3   李可  秋季  青岛  674  125   84250\n",
       "2  MP3   李亮  春季  上海  720  125   90000\n",
       "3  MP3   李亮  夏季  上海  641  125   80125\n",
       "4  MP3   张平  春季  上海  721  125   90125\n",
       "5  MP3   张平  夏季  青岛  384  125   48000\n",
       "6  MP3  周顺利  夏季  上海  354  125   44250\n",
       "7  MP3  周顺利  秋季  青岛  841  125  105125"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "mydf1 = pd.read_excel('myexcel1.xls',sheet_name=1)\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4876"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mydf1.数量.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     68291\n",
       "1     85049\n",
       "2     90845\n",
       "3     80891\n",
       "4     90971\n",
       "5     48509\n",
       "6     44729\n",
       "7    106091\n",
       "dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#mydf1.sum()\n",
    "\n",
    "#mydf1.loc[mydf1['城市'] == '上海'].数量.sum()\n",
    "#mydf1.sum(numeric_only=True)\n",
    "mydf1.sum(axis=1,numeric_only=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'mydf1' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[1], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[43mmydf1\u001b[49m\u001b[38;5;241m.\u001b[39mquery(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m城市 == \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m上海\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m & 数量 ==720\u001b[39m\u001b[38;5;124m\"\u001b[39m)\u001b[38;5;241m.\u001b[39m销售额\u001b[38;5;241m.\u001b[39msum\n",
      "\u001b[1;31mNameError\u001b[0m: name 'mydf1' is not defined"
     ]
    }
   ],
   "source": [
    "mydf1.query(\"城市 == '上海' & 数量 ==720\").销售额.sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>日期</th>\n",
       "      <th>性别</th>\n",
       "      <th>工龄</th>\n",
       "      <th>工资</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>姓名</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>赵可佳</th>\n",
       "      <td>100001</td>\n",
       "      <td>2021-12-18</td>\n",
       "      <td>女</td>\n",
       "      <td>5</td>\n",
       "      <td>5869.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>张可</th>\n",
       "      <td>100012</td>\n",
       "      <td>2021-12-19</td>\n",
       "      <td>男</td>\n",
       "      <td>8</td>\n",
       "      <td>7256.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>周可</th>\n",
       "      <td>100003</td>\n",
       "      <td>2021-12-20</td>\n",
       "      <td>女</td>\n",
       "      <td>4</td>\n",
       "      <td>6895.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>徐南</th>\n",
       "      <td>100004</td>\n",
       "      <td>2021-12-21</td>\n",
       "      <td>男</td>\n",
       "      <td>3</td>\n",
       "      <td>7289.72</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         编号         日期 性别  工龄       工资\n",
       "姓名                                    \n",
       "赵可佳  100001 2021-12-18  女   5  5869.32\n",
       "张可   100012 2021-12-19  男   8  7256.34\n",
       "周可   100003 2021-12-20  女   4  6895.89\n",
       "徐南   100004 2021-12-21  男   3  7289.72"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = {  \"编号\":[100001,100012,100003,100004],\n",
    "          \"日期\":pd.date_range('20211218', periods=4),\n",
    "          \"姓名\":[\"赵可佳\",\"张可\",\"周可\",\"徐南\"],\n",
    "          \"性别\":['女','男','女','男'],\n",
    "          \"工龄\":[5,8,4,3],\n",
    "          \"工资\":[5869.32,7256.34,6895.89,7289.72]\n",
    "       }\n",
    "mydf1 = pd.DataFrame(data) \n",
    "mydf1.set_index(['姓名'],inplace=True)\n",
    "display(mydf1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mydf1.工龄.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "编号    100005.0000\n",
       "工龄         5.0000\n",
       "工资      6827.8175\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mydf1.mean(numeric_only=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6382.605"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mydf1.loc[mydf1['性别']=='女'].工资.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>日期</th>\n",
       "      <th>性别</th>\n",
       "      <th>工龄</th>\n",
       "      <th>工资</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>姓名</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>周可</th>\n",
       "      <td>100003</td>\n",
       "      <td>2021-12-20</td>\n",
       "      <td>女</td>\n",
       "      <td>4</td>\n",
       "      <td>6895.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>徐南</th>\n",
       "      <td>100004</td>\n",
       "      <td>2021-12-21</td>\n",
       "      <td>男</td>\n",
       "      <td>3</td>\n",
       "      <td>7289.72</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        编号         日期 性别  工龄       工资\n",
       "姓名                                   \n",
       "周可  100003 2021-12-20  女   4  6895.89\n",
       "徐南  100004 2021-12-21  男   3  7289.72"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#mydf1.filter(like='可',axis=0).工龄.mean()\n",
    "myx = mydf1.filter(regex='可$',axis=0).工龄.mean()\n",
    "mydf1.query(\"工龄 < @myx & 工资 >= 6000\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "800"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "mydf1 = pd.read_csv('mycl.csv')\n",
    "#display(mydf1)\n",
    "mydf1.数量.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>月份</th>\n",
       "      <th>水果名</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>金额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>202101</td>\n",
       "      <td>西瓜</td>\n",
       "      <td>400</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1520.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>202102</td>\n",
       "      <td>西瓜</td>\n",
       "      <td>450</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1710.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>202103</td>\n",
       "      <td>西瓜</td>\n",
       "      <td>481</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1683.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>202104</td>\n",
       "      <td>西瓜</td>\n",
       "      <td>495</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1732.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        月份 水果名   数量   单价      金额\n",
       "18  202101  西瓜  400  3.8  1520.0\n",
       "19  202102  西瓜  450  3.8  1710.0\n",
       "20  202103  西瓜  481  3.5  1683.5\n",
       "21  202104  西瓜  495  3.5  1732.5"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#mydf1.loc[mydf1['数量'] >mydf1['数量'].max()*0.6]\n",
    "#mydf1.max(numeric_only=True)\n",
    "#mydf1.loc[(mydf1['单价']>mydf1.单价.max()*0.8) & (mydf1['金额'] <mydf1.金额.max()*0.9)]\n",
    "mya = mydf1.单价.max()*0.6\n",
    "myb = mydf1.金额.max()*0.95\n",
    "myc = mydf1.数量.mean()\n",
    "mydf1.query(\"(单价 >@mya) & (金额 < @myb) & (数量 > @myc) & (水果名 != '苹果')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>电器产品</th>\n",
       "      <th>业务员</th>\n",
       "      <th>时间</th>\n",
       "      <th>城市</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李可</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>541</td>\n",
       "      <td>125</td>\n",
       "      <td>67625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李可</td>\n",
       "      <td>秋季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>674</td>\n",
       "      <td>125</td>\n",
       "      <td>84250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李亮</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>720</td>\n",
       "      <td>125</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李亮</td>\n",
       "      <td>夏季</td>\n",
       "      <td>上海</td>\n",
       "      <td>641</td>\n",
       "      <td>125</td>\n",
       "      <td>80125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MP3</td>\n",
       "      <td>张平</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>721</td>\n",
       "      <td>125</td>\n",
       "      <td>90125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MP3</td>\n",
       "      <td>张平</td>\n",
       "      <td>夏季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>384</td>\n",
       "      <td>125</td>\n",
       "      <td>48000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MP3</td>\n",
       "      <td>周顺利</td>\n",
       "      <td>夏季</td>\n",
       "      <td>上海</td>\n",
       "      <td>354</td>\n",
       "      <td>125</td>\n",
       "      <td>44250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MP3</td>\n",
       "      <td>周顺利</td>\n",
       "      <td>秋季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>841</td>\n",
       "      <td>125</td>\n",
       "      <td>105125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  电器产品  业务员  时间  城市   数量   单价     销售额\n",
       "0  MP3   李可  春季  上海  541  125   67625\n",
       "1  MP3   李可  秋季  青岛  674  125   84250\n",
       "2  MP3   李亮  春季  上海  720  125   90000\n",
       "3  MP3   李亮  夏季  上海  641  125   80125\n",
       "4  MP3   张平  春季  上海  721  125   90125\n",
       "5  MP3   张平  夏季  青岛  384  125   48000\n",
       "6  MP3  周顺利  夏季  上海  354  125   44250\n",
       "7  MP3  周顺利  秋季  青岛  841  125  105125"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "44250"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "mydf1 = pd.read_excel('myexcel1.xls',sheet_name=1)\n",
    "display(mydf1)\n",
    "mydf1.销售额.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "电器产品    8\n",
       "业务员     8\n",
       "时间      8\n",
       "城市      8\n",
       "数量      8\n",
       "单价      8\n",
       "销售额     8\n",
       "dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#mydf1.min(numeric_only=True)\n",
    "#mydf1.loc[mydf1['时间']=='春季'].销售额.min()\n",
    "#mydf1.loc[(mydf1.loc[mydf1['时间']=='春季'].销售额.min()) & (mydf1['业务员'] != '李可')]\n",
    "mydf1.count()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
